﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data;
using System.Data.SqlClient;

namespace University.Workshop.DataAccess
{
    public static class UserDataAccess
    {
        public static bool InsertNewUser(Manager newUser, string typeManager) 
        {
            if (newUser != null)
            {
                string stringConnection = "insert into Manager (CiManager,FirstName,LastName,Address,CellPhone,Phone,DateInscription,Email,Gender,UserName,Password,StateManager,ImageManager) values('" + newUser.CIManager + "','" + newUser.FirstName + "','" + newUser.LastName + "','" + newUser.Address + "'," + newUser.CellPhone + "," + newUser.Phone + ",'" + newUser.DateInscription + "','" + newUser.Email + "','" + newUser.Gender + "','" + newUser.UserName + "','" + newUser.Password + "','" + newUser.StateManager + "','" + newUser.ImageManager + "') ";
                int idRole = SearchRole(typeManager);
                string stringConnectionManagerRole = "insert into ManagerRole(Manager_CiManager,Role_IdRole)Values("+newUser.CIManager+","+idRole +")";
                if (ExecuteConsult(stringConnection))
                {
                    return ExecuteConsult(stringConnectionManagerRole);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        private static int SearchRole(string typeManager)
        {
            string stringConnection = "select IdRole from Role where NameRole = '"+typeManager+"'";
            using (DBConnection connection = new DBConnection())
            {
                DataTable data= connection.ConsultationSelectDataTable(stringConnection);
                return int.Parse(data.Rows[0]["IdRole"].ToString());
            }
        }
        public static bool ModifyUser(Manager user, string idUser)
        {
            if (user != null)
            {
                string stringConnection = "update Manager set CiManager='" + user.CIManager + "',FirstName='" + user.FirstName + "',LastName='" + user.LastName + "',Address='" + user.Address + "',CellPhone=" + user.CellPhone + ",Phone=" + user.Phone + ",Email='" + user.Email + "',Gender='" + user.Gender + "',ImageManager='" + user.ImageManager + "' where CiManager='" + idUser + "'";
                return ExecuteConsult(stringConnection);
            }
            else
            {
                return false;
            }
        }

        public static bool DisableUser(string idUser)
        {
            string stringConnection = "update Manager set StateManager=0 where CiManager='" + idUser + "'";
            return ExecuteConsult(stringConnection);
        }
        public static bool ModifyPasswordUser(string userName, string password, string newPassword) 
        {
            string stringConnection = "update Manager set Password='"+newPassword+"' where UserName='"+userName+"' and Password='"+password+"'";
            return ExecuteConsult(stringConnection);
        }

        private static bool ExecuteConsult(string stringConnection)
        {
            using (DBConnection connection = new DBConnection())
            {
                if (connection.CommandExecuteConsult(stringConnection))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static DataTable LoadDataGridViewUser() 
        {
            string stringConnection = "Select * From Manager where StateManager=1";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }

        }
        public static DataTable LoadDataGridViewSearchUser(string searchUser)
        {
            string stringConnection = "Select * From Manager where CiManager like'%"+ searchUser +"%'";
            using (DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return connection.ConsultationSelectDataTable(stringConnection);
                }
                else
                {
                    stringConnection = "Select * From Manager where FirstName like'%" + searchUser + "%'";
                    if (connection.ConsultationSelect(stringConnection))
                    {
                        return connection.ConsultationSelectDataTable(stringConnection);
                    }
                    else
                    {
                        stringConnection = "Select * From Manager where LastName like'%" + searchUser + "%'";
                        if (connection.ConsultationSelect(stringConnection))
                        {
                            return connection.ConsultationSelectDataTable(stringConnection);
                        }
                        else
                        {
                            return null;
                        }

                    }
                }
            }
        }
        public static DataSet ExtractLogOnUser(string userName, string password)
        {
            string stringConnection = "Select FirstName, LastName, UserName,CiManager  From Manager Where UserName ='"+ userName +"' and Password ='"+password+"' and StateManager=1";
            using (DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return connection.ExtractData(stringConnection);
                }
                else
                {
                    return null;
                }                
            }
        }
        public static bool UserExists(string userName) 
        {
            string stringConnection = "select * from Manager where UserName='"+userName+"'";
            using (DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static DataSet LoadDataUser(string searchUser)
        {
            string stringConnection = "select FirstName, LastName from Manager where CiManager='" + searchUser + "'";
            using (DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return connection.ExtractData(stringConnection);
                }
                else
                {
                    return null;
                }
            }
        }
    }
}
